﻿using BLL;
using System;
using System.Data;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Thksoft.MXReport
{
    public partial class TheirFundsReports : BaseUI.BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("YearNum", Type.GetType("System.String"));
                dt.Columns.Add("Year", Type.GetType("System.String"));
                object[] parm = new object[2];
                int ye = Convert.ToInt32(DateTime.Now.AddYears(-5).Year.ToString());
                for (int i = ye; i <= Convert.ToInt32(DateTime.Now.AddYears(5).Year.ToString()); i++)
                {
                    parm[0] = i;
                    parm[1] = i + "";
                    dt.Rows.Add(parm);
                }
                DropDownList1.DataSource = dt;
                DropDownList1.DataValueField = "YearNum";
                DropDownList1.DataTextField = "Year";
                DropDownList1.DataBind();
                DropDownList1.SelectedValue = DateTime.Now.Year.ToString();

                hidCompanyId.Value = "";
                Panel1.Visible = false;
                ClientScript.RegisterStartupScript(GetType(), "XS", "XS()", true);
                DropDownList2.SelectedValue = DateTime.Now.Month.ToString();
                TextChanged();
            }
            else if (cz.Value.Trim().ToString().Split('@')[0].ToString().Trim() == "Detailed")
            {
                string id = cz.Value.Trim().Split('@')[1].ToString().Split('_')[0].ToString();
                string WeekNum = cz.Value.Trim().Split('@')[1].ToString().Split('_')[1].ToString();
                Detailed(id, WeekNum);
                pnl.Visible = false;
                Panel1.Visible = true;
                ClientScript.RegisterStartupScript(GetType(), "MX", "MX()", true);
                cz.Value = "";
            }
        }

        #region 显示详细信息

        private void Detailed(string id, string YearMonthWeekNum)
        {
            #region sql语句

            string weekNum = YearMonthWeekNum.Trim().ToString().Substring(YearMonthWeekNum.Trim().ToString().Length - 1).Trim();
            string sql = @"select 公司名称, 项目名称, 截止上周资金计划, 截止上周累计资金, 本周资金计划,父级编号
  from (select decode(fa.公司名称, null, fs.公司名称, fa.公司名称) 公司名称,
               decode(fs.项目名称, null, fa.项目名称, fs.项目名称) 项目名称,
               nvl(截止上周资金计划, 0) 截止上周资金计划,
               nvl(decode(fs.截止上周累计资金, null,fa.截止上周累计资金), 0) 截止上周累计资金,
               nvl(fs.本周资金计划, 0) 本周资金计划,
               decode(fa.项目编号, null, fs.项目编号, fa.项目编号) 项目编号,
               decode(fs.公司编号, null, fa.公司编号, fs.公司编号) 公司编号,
               decode(fs.m_id,null,fa.m_id,fs.m_id) m_id,
               decode(fs.fatherid,null,fa.fatherid,fs.fatherid) 父级编号
          from (

          select f.pro_id 项目编号,
                       CASE
                         when s.m_id = 0 then
                          (select fd.pro_name
                             from qs_fundplan fd
                            where fd.pro_id = f.pro_id)
                         else
                          (select fm.projectname
                             from qs_fundmonplan fm
                            where fm.id = s.m_id)
                       end 项目名称,
                       m.n_company_id 公司编号,
                       c.cmy_name 公司名称,
                       sum(s.endlastweekusefund) 截止上周累计资金,

                       sum(s.tswkfund) 本周资金计划,
                       s.m_id,
                      f.fatherid
                  from qs_week_son s,
                       qs_week_main      m,
                       qs_fundplan       f,
                       sys_company       c
                 where s.mian_id = m.w_id
                   and s.n_fundid = f.pro_id
                   and m.n_company_id = c.cmy_id
                   and m.issave = 1
                   and m.w_id=" + id + @"
                 group by f.pro_id,
                          s.m_id,
                          f.pro_name,
                          m.n_company_id,
                          c.cmy_name,
                      f.fatherid
                 order by m.n_company_id, f.pro_id

                 ) fs
          full join

         (
         select f.pro_id 项目编号,
                CASE
                  when s.m_id = 0 then
                   (select fd.pro_name
                      from qs_fundplan fd
                     where fd.pro_id = f.pro_id)
                  else
                   (select fm.projectname
                      from qs_fundmonplan fm
                     where fm.id = s.m_id)
                end 项目名称,
                m.n_company_id 公司编号,
                c.cmy_name 公司名称,
                sum(s.endlastweekusefund) 截止上周累计资金,
                sum(s.tswkfund) 截止上周资金计划,
                s.m_id,
                f.fatherid
           from qs_week_son s,
                qs_week_main      m,
                qs_fundplan       f,
                sys_company       c
          where s.mian_id = m.w_id
            and s.n_fundid = f.pro_id
            and m.n_company_id = c.cmy_id
            and m.issave = 1

            and m.m_month ='" + YearMonthWeekNum.Trim().Substring(YearMonthWeekNum.Trim().Length - 1).Trim().ToString() + @"' and m.week_num between '" + YearMonthWeekNum.Trim().ToString().Substring(0, 6).Trim() + @"1' and '" + YearMonthWeekNum.Trim() + (Convert.ToInt32(weekNum) - 1).ToString() + @"'
          group by f.pro_id, s.m_id, f.pro_name, m.n_company_id, c.cmy_name,f.fatherid
          order by m.n_company_id, f.pro_id

          ) fa
            on fs.项目编号 = fa.项目编号
           and fs.m_id = fa.m_id
           and fs.项目名称 = fa.项目名称
           and fs.公司编号 = fa.公司编号
         order by fs.项目编号

         )
 order by 公司编号, 项目编号,m_id";

            #endregion sql语句

            DataTable dt = new BaseBLL().Query(sql);
            ViewState["database2"] = dt;
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

        #endregion 显示详细信息

        /// <summary>
        /// 查询事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnQuery_Click(object sender, EventArgs e)
        {
            //gvReport.Attributes.Add("style", "table-layout:fixed");

            //DataTable dt = getdatatable();
            //gvReport.DataSource = dt;
            //gvReport.DataBind();

            string sql = @"select wm.w_id 编号,
       b.n_business_id 业务编号,
       b.n_business_name 业务名称,
       (select c.cmy_name
          from sys_company c
         where c.cmy_id = wm.n_company_id) 公司名称,
       wm.week_num 申请周数,
       (select sum(s.tswkfund) from qs_week_son s,qs_fundplan f where s.mian_id= wm.w_id and  f.fatherid=0 and s.n_fundid=f.pro_id) money
    from qs_week_main wm, qs_business b
where b.n_business_id =wm.n_business
and wm.issave=1
and wm.m_month='" + (DropDownList1.SelectedValue.ToString() + (Convert.ToInt32(DropDownList2.SelectedValue.ToString()) < 10 ? "0" + DropDownList2.SelectedValue.ToString() : DropDownList2.SelectedValue.ToString())) + "'";
            if (hidCompanyId.Value.Trim().ToString() != "")
            {
                sql += " \r\nand wm.n_company_id in (" + hidCompanyId.Value.Trim().ToString() + ") ";
            }
            DataTable dt = new BaseBLL().Query(sql);
            ViewState["database"] = dt;
            gvReport.DataSource = dt;
            gvReport.DataBind();
            pnl.Visible = true;
            Panel1.Visible = false;
            ClientScript.RegisterStartupScript(GetType(), "XS", "XS()", true);
        }

        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public DataTable getdatatable()
        {
            //选择公司
            if (this.hidCompanyId.Value == "")
            {
                hidCompanyId.Value = "";
                MessageBox.Show(Page, "【温馨提示】请选择公司！");
                return null;
            }

            string str = "";
            string companyId = this.hidCompanyId.Value.ToString();
            string s = DropDownList1.SelectedValue.Trim().ToString();
            int mt = Convert.ToInt32(DropDownList2.SelectedValue.Trim().ToString());
            if (mt > 0 && mt < 10)
            {
                str += s + "0" + mt;
            }
            else
            {
                str = s + mt;
            }

            #region 打印传值

            string strval = companyId + "&" + str + "&";
            this.printargs.Value = strval;

            DataTable company = new Sys_CompanyBLL().SelectCompanys(CurrentUserCompanyID.ToString());
            string comName = "";
            if (company != null && company.Rows.Count > 0)
            {
                foreach (DataRow dr in company.Rows)
                {
                    comName += dr["cmy_name"].ToString() + ",";
                }
            }
            int n = comName.LastIndexOf(',');
            string sm = comName.Remove(n);
            Session["comName"] = sm;

            #endregion 打印传值

            //lbltitle.InnerHtml = DropDownList1.SelectedValue + "年" + DropDownList2.SelectedValue + "月第" + DropDownList3.SelectedValue + "周资金明细";

            //DataTable dt = new Qs_Week_SonBLL().SelectReport(companyId, str, DropDownList3.SelectedValue.Trim().ToString());
            return null;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void gvReport_PreRender(object sender, EventArgs e)
        {
            for (int rowIndex = gvReport.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            {
                GridViewRow row = gvReport.Rows[rowIndex];
                GridViewRow previousRow = gvReport.Rows[rowIndex + 1];
                if (row.Cells[0].Text == previousRow.Cells[0].Text)
                {
                    row.Cells[0].RowSpan = previousRow.Cells[0].RowSpan < 2 ? 2 : previousRow.Cells[0].RowSpan + 1;
                    previousRow.Cells[0].Visible = false;
                }
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void GridView1_PreRender(object sender, EventArgs e)
        {
            for (int rowIndex = GridView1.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            {
                GridViewRow row = GridView1.Rows[rowIndex];
                GridViewRow previousRow = GridView1.Rows[rowIndex + 1];
                if (row.Cells[0].Text == previousRow.Cells[0].Text)
                {
                    row.Cells[0].RowSpan = previousRow.Cells[0].RowSpan < 2 ? 2 : previousRow.Cells[0].RowSpan + 1;
                    previousRow.Cells[0].Visible = false;
                }
            }
        }

        #region 导出方法

        protected void btnOutput_Click(object sender, EventArgs e)
        {
            string fileName = "周资金明细报表_" + DateTime.Now.ToString("yyyyMMdd HHmmssfff");
            string str = "导出人：" + CurrentUserName + "&nbsp;&nbsp;&nbsp;导出时间：" + DateTime.Now;
            lblTJ.Text = str;
            Label4.Text = str;
            if (pnl.Visible == false)
            {
                ExportExcel(Panel2, fileName);
            }
            else
            {
                gvReport.Columns[gvReport.Columns.Count - 1].Visible = false;
                ExportExcel(pnl, fileName);
                gvReport.Columns[gvReport.Columns.Count - 1].Visible = true;
            }
        }

        /// <summary>
        /// 导出方法
        /// </summary>
        /// <param name="objControl"></param>
        /// <param name="strFileName"></param>
        public void ExportExcel(System.Web.UI.WebControls.WebControl objControl, string strFileName)
        {
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            objControl.RenderControl(oHtmlTextWriter);

            objControl = null;
            System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString().Replace("<td", "<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            //base.VerifyRenderingInServerForm(control);
        }

        #endregion 导出方法

        /// <summary>
        /// 查询方法（画图）
        /// </summary>
        /// <returns></returns>
        public DataTable SelectDt()
        {
            //选择公司
            if (this.hidCompanyId.Value == "")
            {
                MessageBox.Show(Page, "【温馨提示】请选择公司！");
                return null;
            }
            string str = "";
            string companyId = this.hidCompanyId.Value.ToString();
            string s = DropDownList1.SelectedValue.Trim().ToString();
            int mt = Convert.ToInt32(DropDownList2.SelectedValue.Trim().ToString());
            if (mt > 0 && mt < 10)
            {
                str += s + "0" + mt;
            }
            else
            {
                str = s + mt;
            }
            DataTable dt = new Qs_Week_SonBLL().SelectReport(companyId, str, "5");
            return dt;
        }

        #region 计算选择年月后有多少周

        #region 年

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            TextChanged();
        }

        #endregion 年

        #region 月

        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            TextChanged();
        }

        #endregion 月

        #region 方法

        private void TextChanged()
        {
        }

        /// <summary>
        /// 某年某月共计有多少天
        /// </summary>
        /// <param name="Montha"></param>
        /// <param name="Yeara"></param>
        /// <returns></returns>
        private string Day(int Montha, int Yeara)
        {
            string day = "0";
            int[] str = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };

            for (int i = 0; i < str.Count(); i++)
            {
                if (i == (Montha - 1))
                {
                    day = str[i].ToString();
                }
            }
            if (Montha == 2)
            {
                if (Yeara % 4 == 0 && Yeara % 100 != 0)
                {
                    day = "29";
                }
            }
            return day;
        }

        /// <summary>
        /// 得到当前月共有好多周
        /// </summary>
        private int getWeekCount(DateTime dt)
        {
            int currentWeek = 0;
            DateTime dd = new DateTime(dt.Year, dt.Month, 1);
            int xq = (int)dd.DayOfWeek;
            int t1 = 0;
            //当前月的1号不是星期天
            if (xq > 0)
            {
                currentWeek++;
                t1 = 7 - xq;
            }
            int t2 = (dt.Day - t1) % 7;
            int t3 = 0;
            if (t2 != 0)
            {
                t3 = (dt.Day - t1) / 7 + 1;
            }
            else
            {
                t3 = (dt.Day - t1) / 7;
            }
            currentWeek += t3;
            return currentWeek;
        }

        #endregion 方法

        #endregion 计算选择年月后有多少周

        protected void gvReport_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string index = (e.Row.RowIndex + 1).ToString();
                e.Row.Cells[0].Text = index;

                e.Row.Attributes.Add("onmouseover", "cssd=this.style.backgroundColor;this.style.backgroundColor='#d5e7ff'");
                e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=cssd");
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            pnl.Visible = true;
            Panel1.Visible = false;
            ClientScript.RegisterStartupScript(GetType(), "XS", "XS()", true);
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes.Add("onmouseover", "cssd=this.style.backgroundColor;this.style.backgroundColor='#d5e7ff'");
                e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=cssd");
            }
        }

        #region 打印

        protected void btnPrint_Click(object sender, EventArgs e)
        {
            if (pnl.Visible == false)
            {
                #region 打印数据

                try
                {
                    Response.Cookies["title"].Value = "周~资~金~明~细@元";
                    DataTable dt = (DataTable)ViewState["database2"];
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (dt.Columns[i].ColumnName.Trim().ToString() == "父级编号" || dt.Columns[i].ColumnName.Trim().ToString() == "流程编号")
                        {
                            dt.Columns.Remove(dt.Columns[i].ColumnName);
                            i = i == 0 ? 0 : i - 1;
                        }
                        if (dt.Columns[i].ColumnName.Trim().ToString().ToUpperInvariant() == "money")
                        {
                            dt.Columns[i].ColumnName = "总金额";
                        }
                    }

                    Session["DataBase"] = dt;

                    ClientScript.RegisterStartupScript(GetType(), "printPage", "printPage()", true);
                }
                catch (Exception)
                {
                    MessageBox.Show(Page, "请先点击查询！！");
                    ClientScript.RegisterStartupScript(GetType(), "MX", "MX()", true);
                }

                #endregion 打印数据
            }
            else
            {
                #region 打印数据

                try
                {
                    Response.Cookies["title"].Value = "周~资~金~明~细@元";
                    DataTable dt = (DataTable)ViewState["database"];
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (dt.Columns[i].ColumnName.Trim().ToString() == "编号" || dt.Columns[i].ColumnName.Trim().ToString() == "流程编号")
                        {
                            dt.Columns.Remove(dt.Columns[i].ColumnName);
                            i = i == 0 ? 0 : i--;
                        }
                        if (dt.Columns[i].ColumnName.Trim().ToString().ToUpperInvariant() == "MONEY")
                        {
                            dt.Columns[i].ColumnName = "总金额";
                        }
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string str = dt.Rows[i]["申请周数"].ToString();
                        dt.Rows[i]["申请周数"] = "第 " + str.Trim().ToString().Substring(str.Trim().ToString().Length - 1) + " 周";
                    }

                    Session["DataBase"] = dt;

                    ClientScript.RegisterStartupScript(GetType(), "printPage2", "printPage2()", true);
                }
                catch (Exception)
                {
                    MessageBox.Show(Page, "请先点击查询！！");
                    ClientScript.RegisterStartupScript(GetType(), "XS", "XS()", true);
                }

                #endregion 打印数据
            }
        }

        #endregion 打印

        /// <summary>
        /// GridView标题列排序
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void gvReport_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sPage = e.SortExpression;
            string SortOrder, OrderDire;
            SortOrder = ViewState["SortOrder"] == null ? null : (string)ViewState["SortOrder"];
            OrderDire = ViewState["OrderDire"] == null ? null : (string)ViewState["OrderDire"];
            if (SortOrder == sPage)
            {
                if (OrderDire == "Desc")
                    OrderDire = "ASC";
                else
                    OrderDire = "Desc";
            }
            else
            {
                SortOrder = e.SortExpression;
                OrderDire = "ASC";
            }
            ViewState["SortOrder"] = SortOrder;
            ViewState["OrderDire"] = OrderDire;
            BindProjectName(true);
        }

        /// <summary>
        /// 绑定项目名称（非动态）绑定GridView
        /// </summary>
        public void BindProjectName(bool iSsort)
        {
            string sql = @"select wm.w_id 编号,
       b.n_business_id 业务编号,
       b.n_business_name 业务名称,
       (select c.cmy_name
          from sys_company c
         where c.cmy_id = wm.n_company_id) 公司名称,
       wm.week_num 申请周数,
       (select sum(s.tswkfund) from qs_week_son s,qs_fundplan f where s.mian_id= wm.w_id and  f.fatherid=0 and s.n_fundid=f.pro_id) money
    from qs_week_main wm, qs_business b
where b.n_business_id =wm.n_business
and wm.issave=1
and wm.m_month='" + (DropDownList1.SelectedValue.ToString() + (Convert.ToInt32(DropDownList2.SelectedValue.ToString()) < 10 ? "0" + DropDownList2.SelectedValue.ToString() : DropDownList2.SelectedValue.ToString())) + "'";
            if (hidCompanyId.Value.Trim().ToString() != "")
            {
                sql += " \r\nand wm.n_company_id in (" + hidCompanyId.Value.Trim().ToString() + ") ";
            }
            DataTable dt = new BaseBLL().Query(sql);
            ViewState["database"] = dt;

            DataView view = dt.DefaultView;
            if (iSsort)
            {
                string sort = (string)ViewState["SortOrder"] + " " + (string)ViewState["OrderDire"];
                view.Sort = sort;//设置排序
            }

            gvReport.DataSource = view;

            gvReport.DataBind();
        }
    }
}